//
//  InitDbTableHSatisfaction.m
//  NursingManages
//
//  Created by Zhen Huang on 17/7/3.
//  Copyright © 2017年 huangzhen. All rights reserved.
//

#import "InitDbTableHSatisfaction.h"
#import "DBHelper.h"
#import "Model_Hr_Satisfaction.h"
@implementation InitDbTableHSatisfaction


-(NSString*) GetHR_SATISFACTION_DETAILTableSql{
    
    NSString *sql = @"create table if not exists hr_satisfaction_detail\
    (\
    sat_period        text not null,\
    item_code         text not null,\
    item_score        numeric,\
    dept_code         text,\
    table_code        text,\
    sat_des           text,\
    sat_type          text,\
    delete_flag       numeric,\
    modifier          text,\
    status            numeric,\
    item_id           text,\
    sat_des2          text,\
    modify_id         text,\
    temp_col          text,\
    modify_date       text,\
    step_id           text,\
    org_id            text,\
    survey_date       text,\
    reasons           text,\
    upload_flag       numeric,\
    sat_id            text\
    )";
    return sql;
}


- (NSMutableArray *)getHSatisFaction
{
    [DBHelper.DB open];
    
    NSString* sql = [NSString stringWithFormat:@"select * from hr_satisfaction_detail where upload_flag = '0'"];
    FMResultSet *set = [DBHelper.DB executeQuery:sql];
    NSMutableArray *array = [self TransalteToModel:set];
    [DBHelper.DB close];
    return array;
    
 
}

- (NSMutableArray *)getHSatisfaUnUpload
{
    [DBHelper.DB open];
    
    NSString* sql = [NSString stringWithFormat:@"select sum(item_score) item_score,MAX(survey_date) survey_date , MAX(dept_code) dept_code,  MAX(modifier) modifier, MAX(sat_type) sat_type ,  MAX(table_code) table_code , MAX(upload_flag) upload_flag FROM  hr_satisfaction_detail  where upload_flag = '0' GROUP BY table_code  " ];
    NSLog(@"%@",sql);
    FMResultSet *set = [DBHelper.DB executeQuery:sql];
    NSMutableArray *array = [self TransalteToModel:set];
    [DBHelper.DB close];
    return array;

}
- (NSMutableArray *)getHSatisfaDidUpload
{
    [DBHelper.DB open];
    
    NSString* sql = [NSString stringWithFormat:@"select sum(item_score) item_score,MAX(survey_date) survey_date , MAX(dept_code) dept_code,  MAX(modifier) modifier, MAX(sat_type) sat_type ,  MAX(table_code) table_code , MAX(upload_flag) upload_flag FROM  hr_satisfaction_detail  where upload_flag = '1' GROUP BY table_code  " ];
    NSLog(@"%@",sql);
    FMResultSet *set = [DBHelper.DB executeQuery:sql];
    NSMutableArray *array = [self TransalteToModel:set];
    [DBHelper.DB close];
    return array;

}

-(NSMutableArray *) TransalteToModel:(FMResultSet*) set{
    
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        Model_Hr_Satisfaction  *model = [[Model_Hr_Satisfaction alloc]init];
        model.SAT_PERIOD = [set stringForColumn:@"sat_period"];
        model.ITEM_CODE = [set stringForColumn:@"item_code"];
        model.ITEM_SCORE = [[set stringForColumn:@"item_score"]floatValue];
        model.DEPT_CODE = [set stringForColumn:@"dept_code"];
        model.TABLE_CODE = [set stringForColumn:@"table_code"];
        model.SAT_DES = [set stringForColumn:@"sat_des"];
        model.SAT_TYPE = [set stringForColumn:@"sat_type"];
        model.DELETE_FLAG = [[set stringForColumn:@"delete_flag"]integerValue];
        model.MODIFIER =[set stringForColumn:@"modifier"];
        model.STATUS = [[set stringForColumn:@"status"]integerValue];
        model.ITEM_ID = [set stringForColumn:@"item_id"];
        model.SAT_DES2 = [set stringForColumn:@"sat_des2"];
        model.MODIFY_ID = [set stringForColumn:@"modify_id"];
        model.TEMP_COL = [set stringForColumn:@"temp_col"];
        model.MODIFY_DATE =[set stringForColumn:@"modify_date"];
        model.STEP_ID = [set stringForColumn:@"step_id"];
        model.ORG_ID = [set stringForColumn:@"org_id"];
        model.SURVEY_DATE = [set stringForColumn:@"survey_date"];
        model.REASONS = [set stringForColumn:@"reasons"];
        model.SAT_ID = [set stringForColumn:@"sat_id"];
        model.UPLOAD_FLAG = [[set stringForColumn:@"upload_flag"]integerValue];
        [array addObject:model];
        
    }
    
    return array;
}

@end
